﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void SaveICC_ABPOS_MAT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryICC_ABPOS_MAT(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapICC_ABPOS_MAT(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertICC_ABPOS_MAT(dwLocal, 1, ITXDBVERSION);
            }
            UpdateICC_ABPOS_MAT(dwLocal);
        }
        public void MapICC_ABPOS_MAT(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void UpdateICC_ABPOS_MAT(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region===========
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT SET "
                + "ABEND=:ABEND,"
                + "KRS=:KRS,"
                + "LFD=:LFD,"
                + "PROGRP=:PROGRP,"
                + "PROGRPD=:PROGRPD,"
                + "ARTGRP=:ARTGRP,"
                + "ARTGRPD=:ARTGRPD,"
                + "MATGRP=:MATGRP,"
                + "MATGRPD=:MATGRPD,"
                + "MATART=:MATART,"
                + "MATARTD=:MATARTD,"
                + "ABPART=:ABPART,"
                + "ABPVAR=:ABPVAR,"
                + "ABPFARBE=:ABPFARBE,"
                + "ABPLIEFDAT=:ABPLIEFDAT,"
                + "ARWERT=:ARWERT,"
                + "ABPLIEFNR=:ABPLIEFNR,"
                + "ABPANRKR=:ABPANRKR,"
                + "EXTFTYNAME=:EXTFTYNAME,"
                + "FSFINISHING=:FSFINISHING,"
                + "ABPSPGRD=:ABPSPGRD,"
                + "ABPGESSTA=:ABPGESSTA,"
                + "UM=:UM,"
                + "RATIO=:RATIO,"
                + "TQTY=:TQTY,"
                + "PQTY=:PQTY,"
                + "DQTY=:DQTY,"
                + "S=:S,"
                + "M=:M,"
                + "L=:L,"
                + "XL=:XL,"
                + "SP=:SP,"
                + "MP=:MP,"
                + "LP=:LP,"
                + "XLP=:XLP,"
                + "QC=:QC,"
                + "ARTDES=:ARTDES,"
                + "CDES=:CDES,"
                + "SECTION=:SECTION,"
                + "PHOTOLINK=:PHOTOLINK,"
                + "MAINFABRIC=:MAINFABRIC,"
                + "RSSENDDAT=:RSSENDDAT,"
                + "RSGETDATE=:RSGETDATE,"
                + "RSACSENDDATE=:RSACSENDDATE,"
                + "RSACGETDATE=:RSACGETDATE,"
                + "RSACCDATE=:RSACCDATE,"
                + "RSACCCOMMENTS=:RSACCCOMMENTS,"
                + "RSSSTAMPSENDATE=:RSSSTAMPSENDATE,"
                + "EMFSENDDAT=:EMFSENDDAT,"
                + "EAFSENDDAT=:EAFSENDDAT,"
                + "EACSENDATE=:EACSENDATE,"
                + "EREMARK=:EREMARK,"
                + "ECHECKDAT=:ECHECKDAT,"
                + "EFCHECKDAT=:EFCHECKDAT,"
                + "TQTYP=:TQTYP,"
                + "PCQTY=:PCQTY,"
                + "PPQTY=:PPQTY,"
                + "PDQTY=:PDQTY,"
                + "PCDAT=:PCDAT,"
                + "PSDAT=:PSDAT,"
                + "PSLAB=:PSLAB,"
                + "PMOUT=:PMOUT,"
                + "PPENDAT=:PPENDAT,"
                + "PAENDAE=:PAENDAE,"
                + "PSTARTDATE=:PSTARTDATE,"
                + "PAENDSTOPDAT=:PAENDSTOPDAT,"
                + "PQCDAT=:PQCDAT,"
                + "PQC2QTY=:PQC2QTY,"
                + "PQC2REMARK=:PQC2REMARK,"
                + "PREPRTNDAT=:PREPRTNDAT,"
                + "PSAMRTDAT=:PSAMRTDAT,"
                + "PQADAE=:PQADAE,"
                + "PQAISSUE=:PQAISSUE,"
                + "PQAREMARK=:PQAREMARK,"
                + "SY=:SY,"
                + "MY=:MY,"
                + "LY=:LY,"
                + "XLY=:XLY,"
                + "TQTYD=:TQTYD,"
                + "KOLL=:KOLL,"
                + "MPRICE=:MPRICE,"
                + "SPREMARK=:SPREMARK,"
                + "ELABELDATE=:ELABELDATE,"
                + "PRSAMPLEAPP=:PRSAMPLEAPP,"
                + "PCDQTY=:PCDQTY,"
                + "PCRATIO=:PCRATIO,"
                + "EPRODDATE=:EPRODDATE,"
                + "OTDRATIO=:OTDRATIO,"
                + "PQCINDAT=:PQCINDAT,"
                + "PQCMIDDAT=:PQCMIDDAT,"
                + "PQCINREMARK=:PQCINREMARK,"
                + "PQCMIDREMARK=:PQCMIDREMARK "
                + " WHERE  "
                      + "  ABID=:ABID  ";

                    or1.Parameters.Add("ABID", OracleType.NVarChar).Value = dwData["ABID"].ToString();
                    or1.Parameters.Add("ABEND", OracleType.NVarChar).Value = dwData["ABEND"].ToString();
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    or1.Parameters.Add("PROGRP", OracleType.NVarChar).Value = dwData["PROGRP"].ToString();
                    or1.Parameters.Add("PROGRPD", OracleType.NVarChar).Value = dwData["PROGRPD"].ToString();
                    or1.Parameters.Add("ARTGRP", OracleType.NVarChar).Value = dwData["ARTGRP"].ToString();
                    or1.Parameters.Add("ARTGRPD", OracleType.NVarChar).Value = dwData["ARTGRPD"].ToString();
                    or1.Parameters.Add("MATGRP", OracleType.NVarChar).Value = dwData["MATGRP"].ToString();
                    or1.Parameters.Add("MATGRPD", OracleType.NVarChar).Value = dwData["MATGRPD"].ToString();
                    or1.Parameters.Add("MATART", OracleType.NVarChar).Value = dwData["MATART"].ToString();
                    or1.Parameters.Add("MATARTD", OracleType.NVarChar).Value = dwData["MATARTD"].ToString();
                    or1.Parameters.Add("ABPART", OracleType.NVarChar).Value = dwData["ABPART"].ToString();
                    or1.Parameters.Add("ABPVAR", OracleType.NVarChar).Value = dwData["ABPVAR"].ToString();
                    or1.Parameters.Add("ABPFARBE", OracleType.NVarChar).Value = dwData["ABPFARBE"].ToString();
                    or1.Parameters.Add("ABPLIEFDAT", OracleType.NVarChar).Value = dwData["ABPLIEFDAT"].ToString();
                    or1.Parameters.Add("ARWERT", OracleType.NVarChar).Value = dwData["ARWERT"].ToString();
                    or1.Parameters.Add("ABPLIEFNR", OracleType.NVarChar).Value = dwData["ABPLIEFNR"].ToString();
                    or1.Parameters.Add("ABPANRKR", OracleType.NVarChar).Value = dwData["ABPANRKR"].ToString();
                    or1.Parameters.Add("EXTFTYNAME", OracleType.NVarChar).Value = dwData["EXTFTYNAME"].ToString();
                    or1.Parameters.Add("FSFINISHING", OracleType.NVarChar).Value = dwData["FSFINISHING"].ToString();
                    or1.Parameters.Add("ABPSPGRD", OracleType.NVarChar).Value = dwData["ABPSPGRD"].ToString();
                    or1.Parameters.Add("ABPGESSTA", OracleType.NVarChar).Value = dwData["ABPGESSTA"].ToString();
                    or1.Parameters.Add("UM", OracleType.NVarChar).Value = dwData["UM"].ToString();
                    or1.Parameters.Add("RATIO", OracleType.NVarChar).Value = dwData["RATIO"].ToString();
                    or1.Parameters.Add("TQTY", OracleType.NVarChar).Value = dwData["TQTY"].ToString();
                    or1.Parameters.Add("PQTY", OracleType.NVarChar).Value = dwData["PQTY"].ToString();
                    or1.Parameters.Add("DQTY", OracleType.NVarChar).Value = dwData["DQTY"].ToString();
                    or1.Parameters.Add("S", OracleType.NVarChar).Value = dwData["S"].ToString();
                    or1.Parameters.Add("M", OracleType.NVarChar).Value = dwData["M"].ToString();
                    or1.Parameters.Add("L", OracleType.NVarChar).Value = dwData["L"].ToString();
                    or1.Parameters.Add("XL", OracleType.NVarChar).Value = dwData["XL"].ToString();
                    or1.Parameters.Add("SP", OracleType.NVarChar).Value = dwData["SP"].ToString();
                    or1.Parameters.Add("MP", OracleType.NVarChar).Value = dwData["MP"].ToString();
                    or1.Parameters.Add("LP", OracleType.NVarChar).Value = dwData["LP"].ToString();
                    or1.Parameters.Add("XLP", OracleType.NVarChar).Value = dwData["XLP"].ToString();
                    or1.Parameters.Add("QC", OracleType.NVarChar).Value = dwData["QC"].ToString();
                    or1.Parameters.Add("ARTDES", OracleType.NVarChar).Value = dwData["ARTDES"].ToString();
                    or1.Parameters.Add("CDES", OracleType.NVarChar).Value = dwData["CDES"].ToString();
                    or1.Parameters.Add("SECTION", OracleType.NVarChar).Value = dwData["SECTION"].ToString();
                    or1.Parameters.Add("PHOTOLINK", OracleType.NVarChar).Value = dwData["PHOTOLINK"].ToString();
                    or1.Parameters.Add("MAINFABRIC", OracleType.NVarChar).Value = dwData["MAINFABRIC"].ToString();
                    or1.Parameters.Add("RSSENDDAT", OracleType.NVarChar).Value = dwData["RSSENDDAT"].ToString();
                    or1.Parameters.Add("RSGETDATE", OracleType.NVarChar).Value = dwData["RSGETDATE"].ToString();
                    or1.Parameters.Add("RSACSENDDATE", OracleType.NVarChar).Value = dwData["RSACSENDDATE"].ToString();
                    or1.Parameters.Add("RSACGETDATE", OracleType.NVarChar).Value = dwData["RSACGETDATE"].ToString();
                    or1.Parameters.Add("RSACCDATE", OracleType.NVarChar).Value = dwData["RSACCDATE"].ToString();
                    or1.Parameters.Add("RSACCCOMMENTS", OracleType.NVarChar).Value = dwData["RSACCCOMMENTS"].ToString();
                    or1.Parameters.Add("RSSSTAMPSENDATE", OracleType.NVarChar).Value = dwData["RSSSTAMPSENDATE"].ToString();
                    or1.Parameters.Add("EMFSENDDAT", OracleType.NVarChar).Value = dwData["EMFSENDDAT"].ToString();
                    or1.Parameters.Add("EAFSENDDAT", OracleType.NVarChar).Value = dwData["EAFSENDDAT"].ToString();
                    or1.Parameters.Add("EACSENDATE", OracleType.NVarChar).Value = dwData["EACSENDATE"].ToString();
                    or1.Parameters.Add("EREMARK", OracleType.NVarChar).Value = dwData["EREMARK"].ToString();
                    or1.Parameters.Add("ECHECKDAT", OracleType.NVarChar).Value = dwData["ECHECKDAT"].ToString();
                    or1.Parameters.Add("EFCHECKDAT", OracleType.NVarChar).Value = dwData["EFCHECKDAT"].ToString();
                    or1.Parameters.Add("TQTYP", OracleType.NVarChar).Value = dwData["TQTYP"].ToString();
                    or1.Parameters.Add("PCQTY", OracleType.NVarChar).Value = dwData["PCQTY"].ToString();
                    or1.Parameters.Add("PPQTY", OracleType.NVarChar).Value = dwData["PPQTY"].ToString();
                    or1.Parameters.Add("PDQTY", OracleType.NVarChar).Value = dwData["PDQTY"].ToString();
                    or1.Parameters.Add("PCDAT", OracleType.NVarChar).Value = dwData["PCDAT"].ToString();
                    or1.Parameters.Add("PSDAT", OracleType.NVarChar).Value = dwData["PSDAT"].ToString();
                    or1.Parameters.Add("PSLAB", OracleType.NVarChar).Value = dwData["PSLAB"].ToString();
                    or1.Parameters.Add("PMOUT", OracleType.NVarChar).Value = dwData["PMOUT"].ToString();
                    or1.Parameters.Add("PPENDAT", OracleType.NVarChar).Value = dwData["PPENDAT"].ToString();
                    or1.Parameters.Add("PAENDAE", OracleType.NVarChar).Value = dwData["PAENDAE"].ToString();
                    or1.Parameters.Add("PSTARTDATE", OracleType.NVarChar).Value = dwData["PSTARTDATE"].ToString();
                    or1.Parameters.Add("PAENDSTOPDAT", OracleType.NVarChar).Value = dwData["PAENDSTOPDAT"].ToString();
                    or1.Parameters.Add("PQCDAT", OracleType.NVarChar).Value = dwData["PQCDAT"].ToString();
                    or1.Parameters.Add("PQC2QTY", OracleType.NVarChar).Value = dwData["PQC2QTY"].ToString();
                    or1.Parameters.Add("PQC2REMARK", OracleType.NVarChar).Value = dwData["PQC2REMARK"].ToString();
                    or1.Parameters.Add("PREPRTNDAT", OracleType.NVarChar).Value = dwData["PREPRTNDAT"].ToString();
                    or1.Parameters.Add("PSAMRTDAT", OracleType.NVarChar).Value = dwData["PSAMRTDAT"].ToString();
                    or1.Parameters.Add("PQADAE", OracleType.NVarChar).Value = dwData["PQADAE"].ToString();
                    or1.Parameters.Add("PQAISSUE", OracleType.NVarChar).Value = dwData["PQAISSUE"].ToString();
                    or1.Parameters.Add("PQAREMARK", OracleType.NVarChar).Value = dwData["PQAREMARK"].ToString();
                    or1.Parameters.Add("SY", OracleType.NVarChar).Value = dwData["SY"].ToString();
                    or1.Parameters.Add("MY", OracleType.NVarChar).Value = dwData["MY"].ToString();
                    or1.Parameters.Add("LY", OracleType.NVarChar).Value = dwData["LY"].ToString();
                    or1.Parameters.Add("XLY", OracleType.NVarChar).Value = dwData["XLY"].ToString();
                    or1.Parameters.Add("TQTYD", OracleType.NVarChar).Value = dwData["TQTYD"].ToString();
                    or1.Parameters.Add("KOLL", OracleType.NVarChar).Value = dwData["KOLL"].ToString();
                    or1.Parameters.Add("MPRICE", OracleType.NVarChar).Value = dwData["MPRICE"].ToString();
                    or1.Parameters.Add("SPREMARK", OracleType.NVarChar).Value = dwData["SPREMARK"].ToString();
                    or1.Parameters.Add("ELABELDATE", OracleType.NVarChar).Value = dwData["ELABELDATE"].ToString();
                    or1.Parameters.Add("PRSAMPLEAPP", OracleType.NVarChar).Value = dwData["PRSAMPLEAPP"].ToString();
                    or1.Parameters.Add("PCDQTY", OracleType.NVarChar).Value = dwData["PCDQTY"].ToString();
                    or1.Parameters.Add("PCRATIO", OracleType.NVarChar).Value = dwData["PCRATIO"].ToString();
                    or1.Parameters.Add("EPRODDATE", OracleType.NVarChar).Value = dwData["EPRODDATE"].ToString();
                    or1.Parameters.Add("OTDRATIO", OracleType.NVarChar).Value = dwData["OTDRATIO"].ToString();
                    or1.Parameters.Add("PQCINDAT", OracleType.NVarChar).Value = dwData["PQCINDAT"].ToString();
                    or1.Parameters.Add("PQCMIDDAT", OracleType.NVarChar).Value = dwData["PQCMIDDAT"].ToString();
                    or1.Parameters.Add("PQCINREMARK", OracleType.NVarChar).Value = dwData["PQCINREMARK"].ToString();
                    or1.Parameters.Add("PQCMIDREMARK", OracleType.NVarChar).Value = dwData["PQCMIDREMARK"].ToString();
                    #endregion
                    break;
                case 19:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (TQTY,TQTYP,PQTY,SP,LP,MP,XLP,SY,LY,MY,XLY,S,L,M,XL,ABKKDAUFTNR,ABPNEUDAT)"
                    + " =(SELECT 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,ABKKDAUFTNR,ABKNEUDAT FROM ABKOPF K WHERE K.ABKAUFKRS=M.KRS AND K.ABKAUFLFD=M.LFD)"
                    + "  WHERE   COEND=' '  AND ABEND='   '  AND EXISTS (SELECT 1 FROM ABKOPF K WHERE K.ABKAUFKRS=M.KRS AND K.ABKAUFLFD=M.LFD  )";
                    break;
                case 20:

                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (ABPSPGRD,ABPGESSTA,SP,S,SY)=(SELECT  ABPSPGRD,ABPGESSTA,ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='S      ' ) WHERE COEND=' ' AND EXISTS (SELECT 1 FROM ABPOS P"
                    + "   WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='S      '  )";
                    break;
                case 21:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (ABPSPGRD,ABPGESSTA,MP,M,MY)=(SELECT  ABPSPGRD,ABPGESSTA,ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P "
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='M      '    ) WHERE COEND=' ' AND EXISTS (SELECT 1 FROM ABPOS P"
                    + "   WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='M      '   )";
                    break;
                case 22:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (ABPSPGRD,ABPGESSTA,LP,L,LY)=(SELECT  ABPSPGRD,ABPGESSTA,ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='L      '   ) WHERE COEND=' ' AND EXISTS (SELECT 1 FROM ABPOS P"
                    + "   WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='L      '  )";
                    break;
                case 23:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (ABPSPGRD,ABPGESSTA,XLP,XL,XLY)=(SELECT  ABPSPGRD,ABPGESSTA,ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P "
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='XL     '   ) WHERE COEND=' ' AND EXISTS (SELECT 1 FROM ABPOS P"
                    + "   WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='XL     '    )";
                    break;
                case 24:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET TQTY=NVL(S,0)+NVL(L,0)+NVL(M,0)+NVL(XL,0),TQTYP=NVL(SP,0)+NVL(LP,0)+NVL(MP,0)+NVL(XLP,0),TQTYD=NVL(SY,0)+NVL(LY,0)+NVL(MY,0)+NVL(XLY,0),PQTY=NVL(SP,0)+NVL(LP,0)+NVL(MP,0)+NVL(XLP,0)";
                    break;
                case 25:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET DQTY=TQTY-TQTYP,PCDQTY=TQTYP-PCQTY,PCRATIO=ROUND(100*(TQTYP-PCQTY)/TQTYP,1)";
                    break;
                case 31:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT SET SP=0,LP=0,MP=0,XLP=0,SY=0,LY=0,MY=0,XLY=0,S=0,L=0,M=0,XL=0"
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 32:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (SP,S,SY)=(SELECT ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                + "  AND P.ABPGROE='S      ')"
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 33:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (MP,M,MY)=(SELECT ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='M      ') "
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 34:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (LP,L,LY)=(SELECT ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='L      ')"
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 35:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (XLP,XL,XLY)=(SELECT ABPSOLLMENGE7*0.01,ABPEALFM*0.01,ABPSOLLMENGE5*0.01 FROM ABPOS P"
                    + "  WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE"
                    + "  AND P.ABPGROE='XL     ') "
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 36:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET TQTY=NVL(S,0)+NVL(L,0)+NVL(M,0)+NVL(XL,0),TQTYP=NVL(SP,0)+NVL(LP,0)+NVL(MP,0)+NVL(XLP,0),TQTYD=NVL(SY,0)+NVL(LY,0)+NVL(MY,0)+NVL(XLY,0),PQTY=NVL(SP,0)+NVL(LP,0)+NVL(MP,0)+NVL(XLP,0)"
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 37:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET DQTY=TQTY-TQTYP,PCDQTY=TQTYP-PCQTY,PCRATIO=ROUND(100*(TQTYP-PCQTY)/TQTYP,1)"
                     + " WHERE ABEND='   ' AND KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 38:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (ABEND)=(SELECT SUBSTR(MIN(ABPERLKZ)||'   ',0,3) FROM ABPOS P "
                    + " WHERE KRS=ABPAUFKRS AND LFD=ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE)"
                    + " WHERE KRS=:KRS AND LFD=:LFD";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 8:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET (MPRICE)="
                        + " (SELECT MAX(ARWERT) FROM ABPR P  WHERE M.KRS=P.ARAUFKRS AND M.LFD=P.ARAUFLFD )"
                        + " WHERE EXISTS (SELECT 1 FROM  ABPR P WHERE M.KRS=P.ARAUFKRS AND M.LFD=P.ARAUFLFD )";
                    break;
                case 9:
                    or1.CommandText = "UPDATE ICC_ABPOS_MAT M SET ARWERT= MPRICE*TQTYP";
                    break;

                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_ABPOS_MAT(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1 = OracleDB.CreateCommand();
            // APZMANDANT  APZART APZVAR APZANRKRS APZALFDNR APZSTUFE
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE  "
                    + "  ABID=:ABID ";
                    or1.Parameters.Add("ABID", OracleType.NVarChar).Value = dwData["ABID"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE  "
                      + "  ABEND=:ABEND  ORDER BY ABPART,ABPVAR,ABPFARBE,KRS,LFD";
                    or1.Parameters.Add("ABEND", OracleType.NVarChar).Value = dwData["ABEND"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE  "
                    + "  KRS=:KRS AND LFD=:LFD AND ABPART=:ABPART";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["ABPAUFKRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["ABPAUFLFD"].ToString();
                    or1.Parameters.Add("ABPART", OracleType.NVarChar).Value = dwData["ABPART"].ToString();
                    break;
                case 4:
                    string sections = dwData["ARTDES"].ToString().Trim();
                    string art = dwData["ABPART"].ToString().Trim();
                    if (sections == "''")
                    {
                        sections = "NOT IN ('   ')";
                    }
                    else
                    {
                        sections = "IN (" + sections + ") ";
                    }
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE "
                    + " ABPNEUDAT LIKE '%" + dwData["ABPNEUDAT"].ToString().Trim() + "%' "
                    + " AND KRS LIKE '%" + dwData["KRS"].ToString().Trim() + "%' "
                    + " AND LFD LIKE '%" + dwData["LFD"].ToString().Trim() + "%' "
                       + "  AND KOLL=:KOLL AND ABPART LIKE '" + art + "%' AND SECTION " + sections + " ORDER BY KRS,LFD,ABPART,ABPVAR,ABPFARBE";
                    or1.Parameters.Add("KOLL", OracleType.NVarChar).Value = dwData["KOLL"].ToString();
                    break;
                case 5:
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE  "
                    + "   KRS=:KRS AND LFD=:LFD ORDER BY ABPFARBE";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"].ToString();
                    break;
                case 6:
                    or1.CommandText = "SELECT * FROM ICC_ABPOS_MAT WHERE  "
                    + "   KRS=:KRS AND LFD=:LFD ORDER BY ABPFARBE";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["ABKAUFKRS"].ToString();
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["ABKAUFLFD"].ToString();
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_ABPOS_MAT";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
        public void InsertICC_ABPOS_MAT(DataRow dwData, int option, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_ABPOS_MAT(ABID,ABEND,KRS,LFD, PROGRP,ARTGRP,MATGRP,MATART,ABPART, ABPVAR, ABPFARBE,ABPLIEFDAT, MPRICE,"
                    + "ABPLIEFNR,ABPANRKR,EXTFTYNAME,ABPSPGRD, ABPGESSTA, UM,  TQTYP,TQTYD,QC,ARTDES, CDES, SECTION, PHOTOLINK, MAINFABRIC,KOLL,COEND,PCQTY)  "
                    + " SELECT SEQ_ICC_ITX.NEXTVAL,A.* FROM (SELECT '   ' AS ABEND,ABPAUFKRS AS KRS,ABPAUFLFD AS LFD,"
                    + " MAX(FSPROGRP) AS PROGRP, MAX(FSARTGRP) AS ARTGRP, MAX(FSMATGRP) AS MATGRP,MAX(FSMATART) AS MATART,ABPART AS ABPART,ABPVAR AS ABPVAR,"
                    + " ABPFARBE AS ABPFARBE,  MAX(ABPLIEFDAT) AS ABPLIEFDAT,MAX(ARWERT) AS MPRICE,MAX(ABPLIEFNR) AS ABPLIEFNR,"
                    + "MAX(ABPANRKR) AS ABPANRKR,MAX(EX.LSNAME1) AS EXTFTYNAME,MAX(ABPSPGRD) AS ABPSPGRD,MAX(ABPGESSTA) AS ABPGESSTA,"
                    + "'PCS' AS UM, SUM(ABPSOLLMENGE7*0.01) AS TQTYP, SUM(ABPSOLLMENGE5*0.01) AS TQTYD, "
                    + " MAX(ABPNEUBEN) AS QC, MAX(FSABEZ) AS ARTDES,MAX(FSFFBEZ) AS CDES, MAX(FSARTPRGR) AS SECTION,MAX(TRIM('R:/'||FSART)||'.jpg') AS PHOTOLINK,"
                    + " MAX(TRIM(SP.LSNAME1)||FSSMART) AS MAINFABRIC,MAX(FSKOLL),' ',0  FROM ABPOS P LEFT JOIN FSSTAMM ON ABPART=FSART "
                    + " LEFT JOIN FSFARBEN ON FSFART=ABPART AND FSFFARBE=ABPFARBE "
                    + " LEFT JOIN FSSTUELI ON FSSART=FSART  AND FSSZUSCHN='J' "
                    + " LEFT JOIN WASTAMM ON WAART=FSSMART "
                    + " LEFT JOIN ABPR ON ABPAUFKRS=ARAUFKRS AND ABPAUFLFD=ARAUFLFD AND ABPAUFPOS=ARAUFPOS "
                    + " LEFT JOIN LIEFDAT SP ON WALIEFNR=SP.LSLIEFNR "
                    + " LEFT JOIN LIEFDAT EX ON ABPLIEFNR=EX.LSLIEFNR "
                    + "  WHERE NOT EXISTS (SELECT 1 FROM ICC_ABPOS_MAT M WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE)  "
                    + "  GROUP BY ABPAUFKRS,ABPAUFLFD,ABPART,ABPVAR,ABPFARBE) A";
                    break;
                case 3:
                    or1.CommandText = "INSERT INTO ICC_ABPOS_MAT(ABID,ABEND,KRS,LFD, PROGRP,ARTGRP,MATGRP,MATART,ABPART, ABPVAR, ABPFARBE,ABPLIEFDAT, MPRICE,"
                    + "ABPLIEFNR,ABPANRKR,EXTFTYNAME,ABPSPGRD, ABPGESSTA, UM,  TQTYP,TQTYD,QC,ARTDES, CDES, SECTION, PHOTOLINK, MAINFABRIC,KOLL,COEND,PCQTY)  "
                    + " SELECT SEQ_ICC_ITX.NEXTVAL,A.* FROM (SELECT '   ' AS ABEND,ABPAUFKRS AS KRS,ABPAUFLFD AS LFD,"
                    + " MAX(FSPROGRP) AS PROGRP, MAX(FSARTGRP) AS ARTGRP, MAX(FSMATGRP) AS MATGRP,MAX(FSMATART) AS MATART,ABPART AS ABPART,ABPVAR AS ABPVAR,"
                    + " ABPFARBE AS ABPFARBE,  MAX(ABPLIEFDAT) AS ABPLIEFDAT,MAX(ARWERT) AS MPRICE,MAX(ABPLIEFNR) AS ABPLIEFNR,"
                    + "MAX(ABPANRKR) AS ABPANRKR,MAX(EX.LSNAME1) AS EXTFTYNAME,MAX(ABPSPGRD) AS ABPSPGRD,MAX(ABPGESSTA) AS ABPGESSTA,"
                    + "'PCS' AS UM, SUM(ABPSOLLMENGE7*0.01) AS TQTYP, SUM(ABPSOLLMENGE5*0.01) AS TQTYD, "
                    + " MAX(ABPNEUBEN) AS QC, MAX(FSABEZ) AS ARTDES,MAX(FSFFBEZ) AS CDES, MAX(FSARTPRGR) AS SECTION,MAX(TRIM('R:/'||FSART)||'.jpg') AS PHOTOLINK,"
                    + " MAX(TRIM(SP.LSNAME1)||FSSMART) AS MAINFABRIC,MAX(FSKOLL),' ',0  FROM ABPOS P LEFT JOIN FSSTAMM ON ABPART=FSART "
                    + " LEFT JOIN FSFARBEN ON FSFART=ABPART AND FSFFARBE=ABPFARBE "
                    + " LEFT JOIN FSSTUELI ON FSSART=FSART  AND FSSZUSCHN='J' "
                    + " LEFT JOIN WASTAMM ON WAART=FSSMART "
                    + " LEFT JOIN ABPR ON ABPAUFKRS=ARAUFKRS AND ABPAUFLFD=ARAUFLFD AND ABPAUFPOS=ARAUFPOS "
                    + " LEFT JOIN LIEFDAT SP ON WALIEFNR=SP.LSLIEFNR "
                    + " LEFT JOIN LIEFDAT EX ON ABPLIEFNR=EX.LSLIEFNR "
                    + "  WHERE P.ABPAUFKRS=:ABKAUFKRS AND P.ABPAUFLFD=:ABKAUFLFD AND NOT EXISTS (SELECT 1 FROM ICC_ABPOS_MAT M WHERE M.KRS=P.ABPAUFKRS AND M.LFD=P.ABPAUFLFD AND M.ABPART=P.ABPART AND M.ABPVAR=P.ABPVAR AND M.ABPFARBE=P.ABPFARBE)  "
                    + "  GROUP BY ABPAUFKRS,ABPAUFLFD,ABPART,ABPVAR,ABPFARBE) A";
                    or1.Parameters.Add("ABKAUFKRS", OracleType.NVarChar).Value = dwData["ABKAUFKRS"];
                    or1.Parameters.Add("ABKAUFLFD", OracleType.NVarChar).Value = dwData["ABKAUFLFD"];
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteICC_ABPOS_MAT(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();

            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_ABPOS_MAT WHERE  ABID=:ABID ";
                    or1.Parameters.Add("ABID", OracleType.NVarChar).Value = dwData["ABID"];
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_ABPOS_MAT WHERE  "
                    + "  KRS=:KRS AND LFD=:LFD AND ART=:ART ";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"];
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"];
                    or1.Parameters.Add("ART", OracleType.NVarChar).Value = dwData["ART"];
                    break;
                case 3:
                    or1.CommandText = "DELETE FROM ICC_ABPOS_MAT WHERE  "
                      + "  KRS=:KRS AND LFD=:LFD ";
                    or1.Parameters.Add("KRS", OracleType.NVarChar).Value = dwData["KRS"];
                    or1.Parameters.Add("LFD", OracleType.NVarChar).Value = dwData["LFD"];
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
